--取运单概要表，查询指定客户的运单
with tab_bill as (
   select 
     waybill_no 
    from (
            select
                waybill_no
            from
                jms_dwd.dwd_wide_sign_summary_waybill_dt t
            where
                t.dt >= '{{ execution_date | date_add(-30) | cst_ds }}' 
                and t.customer_code in ('J0086680055' ,'J00861879826' ,'J0086769417' ,'J00861092652' ,'J00861114335' ,'J0086968421' ,'J00861652451' ,'J00861618006' ,'J0086792828' ,'J00861860708' ,'J00861898751' ,'J00861918138','J00862096518' ,'J00862096581' ,'J00862096645' ,'J00862096707' ,'J00862096728' ,'J00862096750' ,'J00862096779' ,'J00862096797' ,'J00862234665' ,'J00862234659' ,'J00862234657' ,'J00861875051' ,'J00861812046' ,'J00861812046' ,'J00861822308')
        union all  
            select
                waybill_no
            from
                jms_dwd.dwd_wide_unsign_summary_waybill_dt t
            where
                t.dt >= '{{ execution_date | date_add(-37) | cst_ds }}'
                and t.customer_code in ('J0086680055' ,'J00861879826' ,'J0086769417' ,'J00861092652' ,'J00861114335' ,'J0086968421' ,'J00861652451' ,'J00861618006' ,'J0086792828' ,'J00861860708' ,'J00861898751' ,'J00861918138','J00862096518' ,'J00862096581' ,'J00862096645' ,'J00862096707' ,'J00862096728' ,'J00862096750' ,'J00862096779' ,'J00862096797' ,'J00862234665' ,'J00862234659' ,'J00862234657' ,'J00861875051' ,'J00861812046' ,'J00861812046' ,'J00861822308')
     ) group by waybill_no  
 
),
--取揽收表
tab_taking_bill as (
    select
        waybill_no,
        max(scan_type) as scan_type,
        max(scan_time) as scan_time,
        max(input_site) as scan_site,
        max(scan_user) as scan_user
    from
        jms_dwd.dwd_tab_barscan_taking_base_dt t
    where
        t.dt >= '{{ execution_date | date_add(-30) | cst_ds }}'
     group by 
        waybill_no
),
    tab_bill_all as (
    --到件扫描表明细
    select
        waybill_no,
        scan_type,
        scan_time,
        scan_site,
        scan_user,
        '' as difficult,
        '' as other
    from
        jms_dwd.dwd_tab_barscan_arrival_base_dt t
    where
        t.dt >= '{{ execution_date | date_add(-30) | cst_ds }}'
    union all
    --中心发件
    select
        waybill_no,
        scan_type,
        scan_time,
        scan_site,
        scan_user,
        '' as difficult,
        '' as other
    from
        jms_dwd.dwd_tab_barscan_centersend_base_dt t
    where
        t.dt >= '{{ execution_date | date_add(-30) | cst_ds }}'
    union all
    --代收
    select
        waybill_no,
        scan_type,
        scan_time,
        scan_site,
        scan_user,
        '' as difficult,
        '' as other
    from
        jms_dwd.dwd_tab_barscan_collect_base_dt t
    where
        t.dt >= '{{ execution_date | date_add(-30) | cst_ds }}'
    union all
    --出仓扫描
    select
        waybill_no,
        scan_type,
        scan_time,
        scan_site,
        scan_user,
        '' as difficult,
        '' as other
    from
        jms_dwd.dwd_tab_barscan_deliver_base_dt t
    where
        t.dt >= '{{ execution_date | date_add(-30) | cst_ds }}'
    union all
    --问题件扫描
    select
        waybill_no,
        scan_type,
        scan_time,
        scan_site,
        scan_user,
        problem_type as difficult,
        '' as other
    from
        jms_dwd.dwd_tab_barscan_difficult_base_dt t
    where
        t.dt >= '{{ execution_date | date_add(-30) | cst_ds }}'
    union all
    --签收表
        select
        waybill_no,
        scan_type,
        input_time as scan_time,
        t1.name as scan_site,
        scan_user,
        '' as difficult,
        '' as other
    from
        jms_dwd.dwd_tab_barscan_sign_base_dt t
    join jms_dim.dim_network_whole_massage t1 on t.scan_site_id=t1.id
    where
        t.dt >= '{{ execution_date | date_add(-30) | cst_ds }}'
    union all
    --网点发件
        select
        waybill_no,
        scan_type,
        scan_time,
        scan_site,
        scan_user,
        '' as difficult,
        '' as other
    from
        jms_dwd.dwd_tab_barscan_sitesend_base_dt t
    where
        t.dt >= '{{ execution_date | date_add(-30) | cst_ds }}'
    union all
    --快件揽收
    select
        waybill_no,
        scan_type,
        scan_time,
        input_site as scan_site,
        scan_user,
        '' as difficult,
        '' as other
    from
        jms_dwd.dwd_tab_barscan_taking_base_dt t
    where
        t.dt >= '{{ execution_date | date_add(-30) | cst_ds }}'
    union all
    --入仓扫描
    select
        waybill_no,
        scan_type,
        scan_time,
        input_site as scan_site,
        scan_user,
        '' as difficult,
        '' as other
    from
        jms_dwd.dwd_tab_barscan_warehousing_base_dt t
    where
        t.dt >= '{{ execution_date | date_add(-30) | cst_ds }}'
    union all
    --留仓件扫描
    select
        waybill_no,
        scan_type,
        scan_time,
        scan_site,
        scan_user,
        '' as difficult,
        '' as other
    from
        jms_dwd.dwd_tab_barscan_other_base_dt t
    where
        t.dt >= '{{ execution_date | date_add(-30) | cst_ds }}'
),
    tab_bill_all_r as (
    select
        waybill_no,
        scan_type,
        scan_time,
        scan_site,
        scan_user,
        difficult ,
        other
    from
        (
        select
            waybill_no,
            scan_type,
            scan_time,
            scan_site,
            scan_user,
            max(difficult) as difficult ,
            max(other) as other ,
            row_number() over(partition by waybill_no order by scan_time desc ) as rnk
        from
            tab_bill_all
        group by
            waybill_no,
            scan_type,
            scan_time,
            scan_site,
            scan_user
    ) t
    where
        t.rnk = 1

)
, bill_taking as 
    (
     select
        t.waybill_no ,
        t1.scan_type,
        t1.scan_time,
        t1.scan_site,
        t1.scan_user ,
        t2.scan_type as this_scan_type,
        t2.scan_time as this_scan_time,
        t2.scan_site as this_scan_site,
        t2.scan_user as this_scan_user,
        t2.difficult,
        t2.other
    from
        tab_bill t
    join tab_taking_bill t1 on
        t.waybill_no = t1.waybill_no
    join tab_bill_all_r t2 on
        t.waybill_no = t2.waybill_no

)
insert overwrite table jms_dm.dm_big_customer_waybill_status_dt partition(dt='{{ execution_date | cst_ds }}')
select   t.waybill_no ,
        t.scan_type,
        t.scan_time,
        t.scan_site,
        t.scan_user ,
        t.this_scan_type ,
        t.this_scan_time ,
        t.this_scan_site ,
        t.this_scan_user ,
        t.difficult,
        t.other from bill_taking t
;
